﻿using Apps.Models.PLAN;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.DAL.PLAN
{
    public partial class PLAN_TEMPPURCHASERepository
    {
        public IQueryable<PLAN_TEMPPURCHASEModel> QueryPlanTempPurchaseList(int iAPPROVAL_STATUS = 3)
        {
            /*
             SELECT A.TEMPPURCHASEDETAIL_ID, A.TEMPPURCHASE_ID, A.SUPPLIER_ID, A.MINE_ID, A.PRICE_TYPE, A.CONTRACTPRICE, A.FACTORY_PRICE, A.PLAN_COUNT, A.ARRIVE_STANDARD_PRICE, B.*, cc.SUPPLIER_NAME, dd.MATERIEL_NAME, ee.MINE_NAME
              FROM PLAN_TEMPPURCHASEDETAIL A
              INNER JOIN (
                   SELECT * FROM PLAN_TEMPPURCHASE WHERE APPROVAL_STATUS = 3
              ) B
              ON A.TEMPPURCHASE_ID = B.TEMPPURCHASE_ID
              left join PUB_SUPPLIER cc on a.SUPPLIER_ID = cc.SUPPLIER_ID
              left join PUB_MATERIEL dd on a.MATERIEL_ID = dd.MATERIEL_ID
              left join PUB_MINE ee on a.MINE_ID = ee.MINE_ID
             */
            IQueryable<PLAN_TEMPPURCHASEModel> list = from a in Context.PLAN_TEMPPURCHASEDETAIL
                                                      join b in Context.PLAN_TEMPPURCHASE on a.TEMPPURCHASE_ID equals b.TEMPPURCHASE_ID
                                                      join c in Context.PUB_SUPPLIER on a.SUPPLIER_ID equals c.SUPPLIER_ID into supplier
                                                      from tc in supplier.DefaultIfEmpty()
                                                      join d in Context.PUB_MATERIEL on a.MATERIEL_ID equals d.MATERIEL_ID into materiel
                                                      from td in materiel.DefaultIfEmpty()
                                                      join e in Context.PUB_MINE on a.MINE_ID equals e.MINE_ID into mine
                                                      from te in mine.DefaultIfEmpty()
                                                      where b.APPROVAL_STATUS == iAPPROVAL_STATUS
                                                      orderby b.TEMPLATE_ID
                                                      select new PLAN_TEMPPURCHASEModel()
                                                      {
                                                          APPROVAL_STATUS = b.APPROVAL_STATUS,
                                                          PLAN_COUNT = a.PLAN_COUNT,
                                                          ARRIVE_STANDARD_PRICE = a.ARRIVE_STANDARD_PRICE,
                                                          COMPILE_TIME = b.COMPILE_TIME,
                                                          COMPILE_USER = b.COMPILE_USER,
                                                          CONTRACTPRICE = a.CONTRACTPRICE,
                                                          CURRENT_STOCK = b.CURRENT_STOCK,
                                                          ESTIMATE_COAL = b.ESTIMATE_COAL,
                                                          FACTORY_PRICE = a.FACTORY_PRICE,
                                                          MARK = a.MARK,
                                                          MATERIEL_NAME = td.MATERIEL_NAME,
                                                          MINE_ID = a.MINE_ID,
                                                          MINE_NAME = te.MINE_NAME,
                                                          MONTH = b.MONTH,
                                                          ODD_NUMBER = b.ODD_NUMBER,
                                                          PLANNED_PURCHASE = b.PLANNED_PURCHASE,
                                                          PRICE_TYPE = a.PRICE_TYPE,
                                                          SUPPLIER_ID = a.SUPPLIER_ID,
                                                          SUPPLIER_NAME = tc.SUPPLIER_NAME,
                                                          TEMPLATE_ID = b.TEMPLATE_ID,
                                                          TEMPPURCHASEDETAIL_ID = a.TEMPPURCHASEDETAIL_ID,
                                                          TEMPPURCHASE_ID = b.TEMPPURCHASE_ID,
                                                          TYPE = b.TYPE,
                                                          YEAR = b.YEAR,
                                                          BUSINESSTYPE = a.BUSINESSTYPE
                                                      };
            return list;
        }
    }
}
